/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package myprogramui.comp.page1comp;

import Globals.AllGlobal;
import component.stylesheet.component.XlsLoaderProgress;
import controller.MyController;
import database.operate.OperateMyDatabaseImpl;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import myprogramui.comp.page2comp.RightTree;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author Administrator
 */
public class OpenExcel {
    //private RightTree tree;
    private MyController controller;
    private String filepath;
    private int progressI=0;
    private int countNow=0;
    private int countMax=0;
    long timelong2=System.currentTimeMillis();
    private ArrayList<String> zcNames=new ArrayList<String>();
    private Connection conn;
    private Statement stat;
    public OpenExcel(String filepath,RightTree tree,MyController controller){
        try {
            //this.tree=tree;
            this.filepath=filepath;
            this.controller=controller;
            Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
            conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
            stat = conn.createStatement();
            
            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(OpenExcel.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(OpenExcel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void doLoad() throws Exception{

        XlsLoaderProgress progress=new XlsLoaderProgress();
        //TreeItem<String> rootItem=this.tree.getRoot();
        
        String extName = filepath.substring(filepath.lastIndexOf(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("."))+1);
        System.out.println(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("来自XLSLOADER"));
        if(extName.equals(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("XLS"))){
            System.out.println(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("来自XLSLOADER的XLS"));
            HSSFWorkbook hssfw=new HSSFWorkbook(new FileInputStream(this.filepath));
            HSSFSheet gZMSheet=hssfw.getSheet(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("工作面"));
            HSSFSheet zCMSheet=hssfw.getSheet(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("钻场"));
            HSSFSheet zKMSheet=hssfw.getSheet(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("钻孔"));
            for(int i=1;i<=gZMSheet.getLastRowNum();i++){
                HSSFRow hssfr=gZMSheet.getRow(i);
                OperateMyDatabaseImpl.doAdd
                    (java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("INSERT INTO GONGZUOMIAN VALUES(")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")+hssfr.getCell(0).getStringCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(1).getStringCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+timelong2+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(3).getStringCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(4).getStringCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(5).getStringCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(6).getStringCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(7).getStringCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(8).getStringCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(9).getNumericCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(10).getNumericCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(11).getNumericCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(12).getNumericCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(13).getNumericCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(14).getNumericCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(15).getNumericCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(",'")+hssfr.getCell(16).getNumericCellValue()+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("'")
                    + java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(");"));
//                TreeItem<String> gzmItem=new TreeItem<String>(hssfr.getCell(0).getStringCellValue());
//                rootItem.getChildren().add(gzmItem);
                String ccmc=hssfr.getCell(0).getStringCellValue();
                for(int j=1;j<=zCMSheet.getLastRowNum();j++){
                    HSSFRow zcHSSFRow=zCMSheet.getRow(j);
//                    long timelong1=System.currentTimeMillis();
//                    if(zcHSSFRow.getCell(5).getDateCellValue()!=null){
//                        timelong1=zcHSSFRow.getCell(5).getDateCellValue().getTime();
//                    }   
//                    TreeItem<String> zcItem;
//                    if(zcHSSFRow.getCell(0)!=null){
//                        String zcNameString=zcHSSFRow.getCell(0).getStringCellValue();
//                        zcItem=new TreeItem<String>(zcNameString);
//                    }else{
//                        zcItem=new TreeItem<String>(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("空钻场无名称"));
//                    }                   
                    if(zcHSSFRow.getCell(4).getStringCellValue().
                            equals(ccmc)){
                        stat.execute
                        ("insert into zuanchang values("
                        + "'"+zcHSSFRow.getCell(0).getStringCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(1).getNumericCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(2).getNumericCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(3).getNumericCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(4).getStringCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(5).getNumericCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(6).getNumericCellValue()+"'"                  
                        + ");");
                        
//                        gzmItem.getChildren().add(zcItem);
                    }
                    String zcmc=zcHSSFRow.getCell(0).getStringCellValue();
                    for(int k=1;k<=zKMSheet.getLastRowNum();k++){
                        //钻场名称
                        
                        HSSFRow zkHSSFRow=zKMSheet.getRow(k);
//                        long timelong2=System.currentTimeMillis();
                        countMax=zKMSheet.getLastRowNum();
//                        if(zkHSSFRow.getCell(20)!=null){
////                            timelong=zkHSSFRow.getCell(20).getDateCellValue().getTime();
//                        }
                        String beizhu="无";
                        if(zkHSSFRow.getCell(13)!=null){
                            beizhu=zkHSSFRow.getCell(13).getStringCellValue();
                        }
                        if(zkHSSFRow.getCell(14)!=null&&zkHSSFRow.getCell(14).getStringCellValue().
                            equals(zcmc)){
                            progress.setValue(++countNow*100/countMax);
                            
                            stat.execute
                            ("insert into zuankong values("
                            + "'"+zkHSSFRow.getCell(0).getStringCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(1).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(2).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(3).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(4).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(5).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(6).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(7).getNumericCellValue()+"'"   
                            + ",'"+zkHSSFRow.getCell(8).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(9).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(10).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(11).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(12).getNumericCellValue()+"'"
                            + ",'"+beizhu+"'"
                            + ",'"+zkHSSFRow.getCell(14).getStringCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(15).getNumericCellValue()+"'"
                            + ");");
//                            TreeItem<String> zkItem;
//                            if(zkHSSFRow.getCell(0)!=null){
//                                zkItem=new TreeItem<String>(zkHSSFRow.getCell(0).getStringCellValue());
//                            }
//                            else{
//                                zkItem=new TreeItem<String>(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("空钻孔无名称"));
//                            }
//                            zcItem.getChildren().add(zkItem);
                        }
                    }
                }
            }
        }else{
            
            XSSFWorkbook hssfw=new XSSFWorkbook(new FileInputStream(this.filepath));
            XSSFSheet gZMSheet=hssfw.getSheet("工作面");
            XSSFSheet zCMSheet=hssfw.getSheet("钻场");
            XSSFSheet zKMSheet=hssfw.getSheet("钻孔");
            XSSFSheet dCMSheet=hssfw.getSheet("地层");
            for(int i=1;i<zCMSheet.getLastRowNum();i++){
                XSSFRow hssfr=zCMSheet.getRow(i);
                zcNames.add(hssfr.getCell(0).getStringCellValue());
            }
            //单独分离出来加快速度
            countMax=zKMSheet.getLastRowNum()+30;
            for(int k=1;k<=zKMSheet.getLastRowNum();k++){
                XSSFRow zkHSSFRow=zKMSheet.getRow(k);
                

                String beizhu=java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("无");
                if(zkHSSFRow.getCell(13)!=null){
                    beizhu=zkHSSFRow.getCell(13).getStringCellValue();
                }
                boolean flag=false;
                for(int i=0;i<zcNames.size();i++){
                    if(zkHSSFRow.getCell(14).getStringCellValue().equals(zcNames.get(i))){
                        flag=true;
                        break;
                    }
                }
                if(zkHSSFRow.getCell(14)!=null&&flag){
                            countNow++;
                            progressI=(countNow*100/countMax);
                            progress.setValue(progressI);
                            
                            stat.execute
                            ("replace into zuankong values("
                            + "'"+zkHSSFRow.getCell(0).getStringCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(1).getNumericCellValue()+"'"
                            + ",'"+(-1)*zkHSSFRow.getCell(2).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(3).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(4).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(5).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(6).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(7).getNumericCellValue()+"'"   
                            + ",'"+zkHSSFRow.getCell(8).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(9).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(10).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(11).getNumericCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(12).getNumericCellValue()+"'"
                            + ",'"+beizhu+"'"
                            + ",'"+zkHSSFRow.getCell(14).getStringCellValue()+"'"
                            + ",'"+zkHSSFRow.getCell(15).getNumericCellValue()+"'"
                            + ");");
                }
            }
            //下面是工作面和钻场
            for(int i=1;i<=gZMSheet.getLastRowNum();i++){
                XSSFRow hssfr=gZMSheet.getRow(i);
                
//                if(hssfr.getCell(2)!=null){
//                    timelong2=hssfr.getCell(2).getDateCellValue().getTime();
//                }
                stat.execute
                    ("replace into gongzuomian values("
                    + "'"+hssfr.getCell(0).getStringCellValue()+"'"
                    + ",'"+hssfr.getCell(1).getStringCellValue()+"'"
                    + ",'"+timelong2+"'"
                    + ",'"+hssfr.getCell(3).getStringCellValue()+"'"
                    + ",'"+hssfr.getCell(4).getStringCellValue()+"'"
                    + ",'"+hssfr.getCell(5).getStringCellValue()+"'"
                    + ",'"+hssfr.getCell(6).getStringCellValue()+"'"
                    + ",'"+hssfr.getCell(7).getStringCellValue()+"'"
                    + ",'"+hssfr.getCell(8).getStringCellValue()+"'"
                    + ",'"+hssfr.getCell(9).getNumericCellValue()+"'"
                    + ",'"+hssfr.getCell(10).getNumericCellValue()+"'"
                    + ",'"+hssfr.getCell(11).getNumericCellValue()+"'"
                    + ",'"+hssfr.getCell(12).getNumericCellValue()+"'"
                    + ",'"+hssfr.getCell(13).getNumericCellValue()+"'"
                    + ",'"+hssfr.getCell(14).getNumericCellValue()+"'"
                    + ",'"+hssfr.getCell(15).getNumericCellValue()+"'"
                    + ",'"+hssfr.getCell(16).getNumericCellValue()+"'"
                    + ");");
//                TreeItem<String> gzmItem=new TreeItem<String>(hssfr.getCell(0).getStringCellValue());
//                rootItem.getChildren().add(gzmItem);
                
                String ccmc=hssfr.getCell(0).getStringCellValue();//采场名称
                for(int j=1;j<=zCMSheet.getLastRowNum();j++){
                    XSSFRow zcHSSFRow=zCMSheet.getRow(j);
                    countNow++;
                    progressI=(countNow*100/countMax);
                    progress.setValue(progressI);
                    if(zcHSSFRow.getCell(4).getStringCellValue().
                            equals(ccmc)){
                        stat.execute
                        ("replace into zuanchang values("
                        + "'"+zcHSSFRow.getCell(0).getStringCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(1).getNumericCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(2).getNumericCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(3).getNumericCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(4).getStringCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(5).getNumericCellValue()+"'"
                        + ",'"+zcHSSFRow.getCell(6).getNumericCellValue()+"'"                  
                        + ");");
                        
//                        gzmItem.getChildren().add(zcItem);
                    }
                    
                }
            }   
            for(int i=1;i<=dCMSheet.getLastRowNum();i++){
                countNow++;
                progressI=(countNow*100/countMax);
                progress.setValue(progressI);
                XSSFRow hssfr=dCMSheet.getRow(i);
                stat.execute
                        ("replace into diceng values("
                        + "'"+hssfr.getCell(0).getStringCellValue()+"'"
                        + ",'"+hssfr.getCell(1).getNumericCellValue()+"'"
                        + ",'"+hssfr.getCell(2).getNumericCellValue()+"'"           
                        + ");");
            }
        }
        controller.getDco().setPage();
        progress.setShowB(false);
//        progress.setValue(100);
        stat.close();
        conn.close();
    }

    public String getFilepath() {
        return filepath;
    }

    public void setFilepath(String filepath) {
        this.filepath = filepath;
    }
    
}
